﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using WF_Business;

namespace ChBusiness.PubInfo
{
    /// <summary>
    /// 
    /// </summary>
    public class TsInfoManager
    {
        Database dbGloab = BasicOperate.CreateNetOfficeDataBase();


        /// <summary>
        /// Inserts the ts info.
        /// </summary>
        /// <param name="strTsr">The STR TSR.</param>
        /// <param name="strBtsr">The STR BTSR.</param>
        /// <param name="strTssj">The STR TSSJ.</param>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="Tsnr">The TSNR.</param>
        /// <returns></returns>
        public int InsertTsInfo(string strTsr,string strBtsr,string strTssj,string strIid,string Tsnr)
        {
            string strSql = string.Format(@"insert into XT_TSINFO t(TSR,BTSR,TSSJ,IID,TSNR) 
                values('{0}','{1}',to_date('{2}','yyyy-mm-dd'),'{3}','{4}')",strTsr,strBtsr,strTssj,
                strIid,Tsnr);

            return dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }


        /// <summary>
        /// Gets the ts info.
        /// </summary>
        /// <param name="strWid">The STR wid.</param>
        /// <param name="strTsr">投诉人.</param>
        /// <param name="strBtsr">被投诉人.</param>
        /// <param name="strFromTssj">起始投诉时间.</param>
        /// <param name="strToTssj">结束投诉时间.</param>
        /// <returns></returns>
        public DataTable GetTsInfo(string strWid, string strTsr, string strBtsr, string strFromTssj, string strToTssj)
        {
            //添加业务删除标识条件 and b.isdelete <>1  addby zhongjian 20091223
            string strSql = "select rownum 序号,t.* from XT_TSINFO t ";
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" where t.iid in (select iid from st_instance b where b.wid='{0}' and b.isdelete <>1)", strWid);
            }
            if (!string.IsNullOrEmpty(strTsr))
            {
                strSql += string.Format(" and TSR like '%{0}%'", strTsr);
            }
            if (!string.IsNullOrEmpty(strBtsr))
            {
                strSql += string.Format(" and BTSR like '%{0}%'", strBtsr);
            }
            if (!string.IsNullOrEmpty(strFromTssj))
            {
                strSql += string.Format(" and TSSJ >= to_date('{0}','yyyy-mm-dd')", strFromTssj);
            }
            if (!string.IsNullOrEmpty(strToTssj))
            {
                strSql += string.Format(" and TSSJ <= to_date('{0}','yyyy-mm-dd')", strToTssj);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// Gets the ts info.
        /// </summary>
        /// <param name="strID">The STR ID.</param>
        /// <returns></returns>
        public DataTable GetTs(string strID)
        {
            string strSql = "select * from XT_TSINFO t ";
            if (!string.IsNullOrEmpty(strID))
            {
                strSql += string.Format(" where t.ID = '{0}'", strID);
            }

            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// UpdateTsInfo the ts info.
        /// </summary>
        /// <param name="strId">The STR ID.</param>
        /// <param name="strTsr">The STR TSR.</param>
        /// <param name="strBtsr">The STR BTSR.</param>
        /// <param name="strTssj">The STR TSSJ.</param>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="Tsnr">The TSNR.</param>
        /// <returns></returns>
        public int UpdateTsInfo(string strId, string strTsr, string strBtsr, string strTssj, string strIid, string Tsnr)
        {
            string strSql = string.Format(@"Update XT_TSINFO set TSR = '{1}',BTSR = '{2}',
               TSSJ = to_date('{3}','yyyy-mm-dd'),IID = '{4}',TSNR = '{5}' where id='{0}'", 
               strId, strTsr, strBtsr, strTssj,strIid, Tsnr);

            return dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }
        
        /// <summary>
        /// UpdateTsInfo the ts info.
        /// </summary>
        /// <param name="strId">The STR ID.</param>
        /// <returns></returns>
        public int DeleteTsInfo(string strId)
        {
            string strSql = string.Format(@"Delete from XT_TSINFO where id='{0}'",strId);

            return dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }

        public DataTable SearchTsInfo(string userid, string s_worktype, string d_s, string d_e, string s_appunit, bool ISsystemUser)
        {
            string sql = string.Empty;           

            //添加业务删除标识条件
            sql = "select rownum , a.IID,a.name,to_char(a.Exbeginmonitor,'YYYY-MM-DD HH24:Mi:SS')  gettime,b.wname , to_char(a.finish_time,'YYYY-MM-DD HH24:Mi:SS') dotime ,(a.ExTotaltime-a.ExUsedtime),a.status,(select u.user_name from sv_work_step t,st_user u where t.prev_wiid =0 and t.userid=u.userid and t.iid=a.iid) username from st_instance a,st_workflow b where 1=1 and a.wid=b.wid";

            if (!string.IsNullOrEmpty(s_worktype))
            {
                if (!s_worktype.Equals("所有类型"))
                {
                    sql += (" and b.wname='" + s_worktype + "'");
                }
                else
                {
                    if (!ISsystemUser)
                    {
                        sql += " and b.wname in (select task from xt_query_right t where t.userid = '" + userid + "')";
                    }
                }
            }
            else//首次展示数据
            {
                if (!ISsystemUser)
                {
                    sql += " and b.wname in (select task from xt_query_right t where t.userid = '" + userid + "' and rownum<2)";
                }
            }
            if (!string.IsNullOrEmpty(d_s) && !string.IsNullOrEmpty(d_e))
            {
                sql += "  and a.finish_time>=to_date('" + d_s + "','YYYY-MM-DD HH24:MI:SS')";
                sql += "  and a.finish_time<=to_date('" + d_e + "','YYYY-MM-DD HH24:MI:SS')";
            }

            if (!string.IsNullOrEmpty(s_appunit))
            {
                sql += string.Format("  and a.name like '%{0}%'", s_appunit);
            }

            System.Data.DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            return dtOut;
        }

        public DataTable Lcmx(string strIid)
        {
            double dHourInEveryDay = SystemConfig.SecondInEveryDay;

            //根据输入的业务ID,查找视图
            string sql1 = string.Format(@"select step,user_name,to_char(accepted_time ,'YYYY-MM-DD HH24:Mi:SS') accepted_time,
                        trunc(exusedtime/{0})||'天'||trunc((mod(exusedtime,{0})/(3600)))||'小时'||trunc((mod(exusedtime,3600)/(60)))||'分钟' exusedtime,exusedtime,isback from sv_work_step a, st_user b where 
                        b.userid=a.userid and a.iid='{1}' order by a.wiid",
            dHourInEveryDay, strIid);

            System.Data.DataTable rs;
            SysParams.OAConnection().RunSql(sql1, out rs);

            return rs;
        }

        public DataTable Ywinfo(string d_s, string d_e, string s_tsman)
        {
            string sql = "";

            DataTable rs = null;

            sql = "select TS_MAN, TS_DATE,TS_INFO,TS_OBJECT, TS_IID,id from st_tousu where 1='1' ";
            if ((!string.IsNullOrEmpty(d_s)) && (!string.IsNullOrEmpty(d_e)))
            {
                sql += ("   and TS_DATE>=to_date('" + d_s + "','YYYY-MM-DD')");
                sql += ("  and TS_DATE<=to_date('" + d_e + "','YYYY-MM-DD')");
            }

            if (!string.IsNullOrEmpty(s_tsman))
            {
                sql += ("  and ts_man='" + s_tsman + "'");
            }

            SysParams.OAConnection().RunSql(sql, out rs);
            return rs;
        }

        public DataTable YwinfoById(string id)
        {
            string sql = "";

            DataTable rs = null;

            sql = "select TS_MAN,to_char(TS_DATE,'YYYY-MM-DD'),TS_INFO,TS_OBJECT, TS_IID,id from st_tousu where id='" + id + "' ";

            SysParams.OAConnection().RunSql(sql, out rs);
            return rs;
        }

        public int DelTsById(string id)
        {
            string sql = "";
            if (!id.Equals(""))
                sql = "delete from st_tousu where id='" + id + "'";
            try
            {
                return SysParams.OAConnection().RunSql(sql);
            }
            catch
            {
                return 0;
            }
        }

        public int AddTs(string tsman, string d_s, string tsinfo, string tsobject, string tsIID)
        {
            string sql = "";
            sql = "insert into st_tousu(TS_MAN,TS_DATE,TS_INFO,TS_OBJECT,TS_IID,TS_CASENAME)";
            sql += ("values('" + tsman + "',to_date('" + d_s + "','YYYY-MM-DD'),'" + tsinfo + "','" + tsobject + "','" + tsIID + "','')");

            try
            {
                return SysParams.OAConnection().RunSql(sql);
            }
            catch
            {
                return 0;
            }
        }
    }
}
